package com.app.utils;

import java.lang.reflect.Field;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.*;

/**
 * Copyright (C) @2020 fgwang.660@gmail.com
 *
 * @author witt
 * @version 1.1
 * @className JDBCTemplate
 * @date 2020-05-31 19:27
 * @description JDBC模板
 */
public class JDBCTemplate {

	// JDBC 驱动
	private static final String DRIVER = "com.mysql.jdbc.Driver";

	// JDBC URL
	private static final String URL = "jdbc:mysql://localhost:3306/business_management?useUnicode=true&characterEncoding=utf-8&useSSL=false";

	// 数据库用户名
	private static final String USERNAME = "root";

	// 数据库密码
	private static final String PASSWORD = "lele";

	/**
	 * 首次类加载的时候  将驱动注册好，只注册一次
	 */
	static {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			throw new NullPointerException("JDBC Driver Not found !");
		}
	}

	/**
	 * 构造器  创建模板对象的同时 构建数据库连接对象
	 */
	public JDBCTemplate() {
	}

	/**
	 * 保证一个JDBCTemplate对象只创建一个数据库连接对象
	 *
	 * @return
	 */
	public Connection getConnection() {
		try {
			return DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}


	/**
	 * 执行SQL的方法 如果是 select 语句  返回 ResultSet对象 , insert 语句  返回自增的id , 其它语句返回影响行数
	 *
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public Object execute(String sql, List<Object> params) throws SQLException {
		PreparedStatement ps = getConnection().prepareStatement(sql);

		// 设置参数
		if (UtilTools.isNotEmpty(params)) {
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
		}

		ResultSet resultSet;
		// select 语句  返回结果集
		if (sql.toLowerCase().contains("select")) {
			// 执行SQL，得到结果
			resultSet = ps.executeQuery();
			return resultSet;
		} else if (sql.toLowerCase().contains("insert")) {
			// insert 语句 返回自增的id
			ps.executeUpdate();

			// 获取insert之后的主键
			resultSet = ps.getGeneratedKeys();

			return resultSet.next() ? resultSet.getInt(1) : - 1;
		} else {
			return ps.executeUpdate();
		}
	}

	/**
	 * 销毁连接对象，statement对象，结果集
	 *
	 * @param connection
	 * @param statement
	 * @param resultSet
	 * @return
	 */
	public void destroy(Connection connection, Statement statement, ResultSet resultSet) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}


	/**
	 * 关闭statement和resultSet对象
	 *
	 * @param statement
	 * @param resultSet
	 * @return
	 */
	public void destroy(Statement statement, ResultSet resultSet) {
		destroy(null, statement, resultSet);
	}

	/**
	 * 关闭connection和statement对象
	 *
	 * @param statement
	 * @param connection
	 * @return
	 */
	public void destroy(Connection connection, Statement statement) {
		destroy(connection, statement, null);
	}

	/**
	 * 关闭resultSet和附属的statement对象
	 *
	 * @param resultSet
	 * @return
	 */
	public void destroy(ResultSet resultSet) {
		if (resultSet != null) {
			try {
				if (! resultSet.isClosed()) {
					try {
						Statement statement = resultSet.getStatement();
						if (statement != null) statement.close();
					} catch (SQLException e) {
						e.printStackTrace();
					} finally {
						try {
							resultSet.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * JDBC    select 查询方法，支持条件查询
	 *
	 * @param connection 数据库连接对象
	 * @param clazz      查询的类
	 * @param whereCause 查询条件
	 * @return 结果集
	 * @throws SQLException
	 */
	public ResultSet select(Connection connection, Class<?> clazz, List<WhereCause> whereCause) throws SQLException {
		StringBuilder sql = new StringBuilder("SELECT ");

		// 拼接查询的列
		String select = String.join(" , ", UtilTools.convertBeanToColumns(clazz));
		sql.append(select);

		// 拼接 from 子句
		sql.append(" FROM ").append(UtilTools.convertBeanToTable(clazz));

		// 拼接查询条件
		if (UtilTools.isNotEmpty(whereCause)) {
			sql.append(" WHERE ").append(WhereCause.getFullWhereCause(whereCause));
		}

		Statement statement = connection.createStatement();
		// 执行SQL
		ResultSet resultSet = statement.executeQuery(sql.toString());
		return resultSet;
	}

	/**
	 * JDBC select 方法，根据类和 查询条件，返回查询结果
	 *
	 * @param clazz      表对应的实体类
	 * @param whereCause 查询条件
	 * @param <T>        类类型
	 * @return
	 * @throws SQLException
	 */
	public <T> List<T> select(Class<T> clazz, List<WhereCause> whereCause) throws SQLException {
		StringBuilder sql = new StringBuilder("SELECT ");

		// 拼接查询的列
		String select = String.join(" , ", UtilTools.convertBeanToColumns(clazz));
		sql.append(select);

		// 拼接 from 子句
		sql.append(" FROM ").append(UtilTools.convertBeanToTable(clazz));

		// 拼接查询条件
		if (UtilTools.isNotEmpty(whereCause)) {
			sql.append(" WHERE ").append(WhereCause.getFullWhereCause(whereCause));
		}

		Connection connection = getConnection();
		Statement statement = connection.createStatement();
		// 执行SQL
		ResultSet resultSet = statement.executeQuery(sql.toString());

		// 获取类中的所有属性
		Field[] fields = UtilTools.reflectAllField(clazz);
		Map<String, Field> fieldMaps = new HashMap<>();
		for (Field field : fields) {
			// key为属性名的全大写，value为 当前属性对象
			fieldMaps.put(field.getName().toUpperCase(), field);
		}
		List<T> result = new ArrayList<>();
		try {
			ResultSetMetaData metaData = resultSet.getMetaData();
			// 获取查到的所有列
			int columnCount = metaData.getColumnCount();
			while (resultSet.next()) {
				// 创建数据对象
				T object = clazz.newInstance();
				// 遍历每一行数据的每一列
				for (int i = 1; i <= columnCount; i++) {
					String column = UtilTools.toLowerCamel(metaData.getColumnName(i)).toUpperCase();
					Object value = resultSet.getObject(i);
					if (UtilTools.isNotEmpty(value) && fieldMaps.containsKey(column)) {
						Field field = fieldMaps.get(column);
						field.setAccessible(true);
						// 映射SQL与JAVA的数据类型
						field.set(object, dataTypeHandler(metaData.getColumnType(i), field.getType(), value));
					}
				}
				result.add(object);
			}
		} catch (IllegalAccessException | InstantiationException e) {
			throw new RuntimeException(e);
		} finally {
			// 关闭资源，回收连接
			destroy(connection, statement, resultSet);
		}
		return result;
	}

	/**
	 * 查询多条记录，无条件查询
	 *
	 * @param clazz
	 * @param <T>
	 * @return
	 * @throws SQLException
	 */
	public <T> List<T> select(Class<T> clazz) throws SQLException {
		return select(clazz, null);
	}


	/**
	 * 查询一条记录  并映射为单个对象
	 *
	 * @param clazz      表对应的类类型
	 * @param whereCause 筛选条件
	 * @param <T>        表对应的类
	 * @return 单个对象
	 * @throws SQLException 如果查询结果数 > 一行 会 throw SQLException
	 */
	public <T> T selectBean(Class<T> clazz, List<WhereCause> whereCause) throws SQLException {
		StringBuilder sql = new StringBuilder("SELECT ");

		// 拼接查询的列
		String select = String.join(" , ", UtilTools.convertBeanToColumns(clazz));
		sql.append(select);

		// 拼接 from 子句
		sql.append(" FROM ").append(UtilTools.convertBeanToTable(clazz));

		// 拼接查询条件
		if (UtilTools.isNotEmpty(whereCause)) {
			sql.append(WhereCause.getFullWhereCause(whereCause));
		}

		Connection connection = getConnection();
		Statement statement = connection.createStatement();
		// 执行SQL
		ResultSet resultSet = statement.executeQuery(sql.toString());

		// 获取类中的所有属性
		Field[] fields = UtilTools.reflectAllField(clazz);
		Map<String, Field> fieldMaps = new HashMap<>();
		for (Field field : fields) {
			// key为属性名的全大写，value为 当前属性对象
			fieldMaps.put(field.getName().toUpperCase(), field);
		}
		T object = null;
		try {
			ResultSetMetaData metaData = resultSet.getMetaData();
			// 获取查到的所有列
			int columnCount = metaData.getColumnCount();
			// 获取一条数据
			if (resultSet.next()) {
				// 创建数据对象
				object = clazz.newInstance();
				// 遍历每一行数据的每一列
				for (int i = 1; i <= columnCount; i++) {
					String column = UtilTools.toLowerCamel(metaData.getColumnName(i)).toUpperCase();
					Object value = resultSet.getObject(i);
					if (UtilTools.isNotEmpty(value) && fieldMaps.containsKey(column)) {
						Field field = fieldMaps.get(column);
						field.setAccessible(true);
						field.set(object, dataTypeHandler(metaData.getColumnType(i), field.getType(), value));
					}
				}
			}
			// 如果还有下一条，抛异常
			if (resultSet.next()) {
				throw new SQLException("The number of query results is greater than one, please check whether the SQL condition is correct, or use the select method .");
			}
		} catch (IllegalAccessException | InstantiationException e) {
			throw new RuntimeException(e);
		} finally {
			// 关闭资源，回收连接
			destroy(connection, statement, resultSet);
		}
		return object;
	}

	/**
	 * JDBC insert 方法 通过反射获取表名、列名，以及属性值
	 *
	 * @param obj 需要插入的对象，对象中的空字段不会被插入
	 * @return 自动生成的主键id
	 * @throws SQLException
	 */
	public Integer insert(Object obj) throws SQLException {
		if (UtilTools.isEmpty(obj)) throw new NullPointerException("The object you pass in is a null .");

		StringBuilder sql = new StringBuilder("INSERT INTO ");
		sql.append(UtilTools.convertBeanToTable(obj.getClass()));

		// 需要insert的列
		List<String> columns = new ArrayList<>();
		// 替换占位的数据
		List<Object> values = new ArrayList<>();
		for (Map.Entry<String, Object> entry : UtilTools.convertBeanDataToTableData(obj).entrySet()) {
			columns.add(entry.getKey());
			values.add(entry.getValue());
		}

		sql.append(" (").append(String.join(",", columns)).append(") ");
		sql.append("VALUES( ");
		String[] placeholder = new String[columns.size()];
		for (int i = 0; i < columns.size(); i++) {
			placeholder[i] = "?";
		}
		sql.append(String.join(", ", placeholder)).append(" )");

		Connection connection = getConnection();
		PreparedStatement ps = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);

		// 使用值替换占位符
		for (int i = 0; i < values.size(); i++) {
			ps.setObject(i + 1, values.get(i));
		}

		// 执行插入
		ps.executeUpdate();

		// 获取insert之后的主键
		ResultSet resultSet = ps.getGeneratedKeys();
		int id = - 1;
		if (resultSet.next()) {
			id = resultSet.getInt(1);

			// 通过反射，给对象注入自动增长的id
			try {
				Field field = UtilTools.findField(obj.getClass(), "id");
				if (UtilTools.isNotEmpty(field)) {
					field.setAccessible(true);
					field.set(obj, id);
				}
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}

		// 关闭资源
		destroy(ps, resultSet);


		return id;
	}


	/**
	 * JDBC update  方法通过反射根据对象更新数据
	 *
	 * @param obj         赋值的对象引用
	 * @param causeFields update 条件，传属性名 如果不传  默认使用id更新
	 * @return 更新的行数
	 * @throws SQLException
	 */
	public Integer update(Object obj, String... causeFields) throws SQLException {
		if (UtilTools.isEmpty(obj)) throw new NullPointerException("The object you pass in is a null .");

		StringBuilder sql = new StringBuilder("UPDATE ");
		sql.append(UtilTools.convertBeanToTable(obj.getClass())).append(" SET ");

		// 需要insert的列
		List<String> columns = new ArrayList<>();
		// 替换占位的数据
		List<Object> values = new ArrayList<>();
		for (Map.Entry<String, Object> entry : UtilTools.convertBeanDataToTableData(obj).entrySet()) {
			columns.add(entry.getKey());
			values.add(entry.getValue());
		}
		// 默认使用 id 条件更新
		if (UtilTools.isEmpty(causeFields)) {
			causeFields = new String[] { "id" };
		}
		// 把属性名转成列名
		causeFields = Arrays.stream(causeFields).map(UtilTools::lowerCamelToColumn).toArray(String[]::new);

		// where值索引
		List<Integer> whereIndex = new ArrayList<>();
		// set值索引
		List<Integer> setIndex = new ArrayList<>();
		// where条件的列
		List<String> where = new ArrayList<>();
		// set条件的列
		List<String> set = new ArrayList<>();

		// 从对象里分离出 where 和 set 的列  以及  值
		for (int i = 0; i < columns.size(); i++) {
			String column = columns.get(i);
			if (UtilTools.contains(causeFields, column)) {
				whereIndex.add(i);
				where.add(column);
			} else { // 是 set 语句,格式column = ? , column = ?
				setIndex.add(i);
				set.add(column);
			}
		}

		// 将set的值和where的值依次组合
		setIndex.addAll(whereIndex);

		// set子句拼接
		sql.append(String.join(" = ? ,", set)).append(" = ? WHERE ");
		// where子句拼接
		sql.append(String.join(" = ? AND ", where)).append(" = ?");

		Connection connection = getConnection();
		PreparedStatement ps = connection.prepareStatement(sql.toString());

		// 使用值替换占位符
		for (int i = 0; i < setIndex.size(); i++) {
			int index = Integer.parseInt(String.valueOf(setIndex.get(i)));
			ps.setObject(i + 1, values.get(index));
		}


		// 执行插入
		int row = ps.executeUpdate();

		// 关闭资源
		destroy(connection, ps);
		return row;
	}


	/**
	 * JDBC delete方法 反射获取表名 ,WhereCause对象做条件
	 *
	 * @param clazz
	 * @param whereCause
	 * @return
	 * @throws SQLException
	 */
	public Integer delete(Class<?> clazz, List<WhereCause> whereCause) throws SQLException {
		if (UtilTools.isEmpty(clazz)) throw new NullPointerException("The object you pass in is a null .");

		StringBuilder sql = new StringBuilder("DELETE FROM ");
		sql.append(UtilTools.convertBeanToTable(clazz));

		sql.append(WhereCause.getFullWhereCause(whereCause));

		Connection connection = getConnection();
		Statement statement = connection.createStatement();

		// 执行更新
		int row = statement.executeUpdate(sql.toString());

		// 关闭资源
		destroy(connection, statement);
		return row;
	}

	/**
	 * 数据类型处理器  映射数据库中的类型与JAVA中的类型，特殊处理的，新增case分支处理即可
	 *
	 * @param type  SQL的数据类型
	 * @param clazz 对应Bean的数据类类型
	 * @param value SQL查询出来的数据
	 * @return 对应Bean类型的数据
	 */
	private Object dataTypeHandler(int type, Class<?> clazz, Object value) {
		switch (type) {
			case Types.BIGINT:
				// bean是Integer类型
				if (Integer.class.isAssignableFrom(clazz)) {
					return ((Long) value).intValue();
				} else {
					return value;
				}
			case Types.TINYINT:
				if (Boolean.class.isAssignableFrom(clazz)) {
					// 处理数据类型
					return ((int) value) == 1;
				}
				return value;
			case Types.TIMESTAMP:
				// bean 是 Date 类型
				if(Date.class.isAssignableFrom(clazz)){
					return value;
					// bean 是 LocalDateTime 类型
				}else if(LocalDateTime.class.isAssignableFrom(clazz)){
					// 库中是java.sql.Timestamp  JAVA是 LocalDateTime
					return ((Timestamp)value).toLocalDateTime();
				}
			default:
				return value;
		}
	}

}
